CREATE OR REPLACE FUNCTION procedure(first text, last text, code text, birthdate Date)
RETURNS void
AS $$
DECLARE
BEGIN
	INSERT INTO person (first_name, last_name, identity_code, birth_date) VALUES (first, last, code, birthdate);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION delete_enterprise(enterprise_id integer)
RETURNS void
AS $$
DECLARE
 	related RECORD;
BEGIN
 	SELECT COUNT(*) INTO related FROM enterprise_person_relation WHERE enterprise_id = enterprise_fk;

	IF related.count > 0 THEN
		RAISE EXCEPTION 'Enterprise related to at least one person';
	END IF;

	SELECT COUNT(*) INTO related FROM employee WHERE enterprise_id = enterprise_fk;

	IF related.count > 0 THEN
		RAISE EXCEPTION 'Enterprise employs at least one person';
	END IF;

	DELETE FROM enterprise WHERE enterprise = enterprise_id;
  	DELETE FROM customer WHERE subject_fk = enterprise_id AND subject_type_fk = 2;
  	DELETE FROM contact WHERE subject_fk = enterprise_id AND subject_type_fk = 2;
  	DELETE FROM address WHERE subject_fk = enterprise_id AND subject_type_fk = 2;
  	DELETE FROM subject_attribute WHERE subject_fk = enterprise_id AND subject_type_fk = 2;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_person(person_id integer)
RETURNS void
AS $$
DECLARE
 	related RECORD;
BEGIN
 	SELECT COUNT(*) INTO related FROM enterprise_person_relation WHERE person_id = person_fk;

	IF related.count > 0 THEN
		RAISE EXCEPTION 'Person related to at least one employer';
	END IF;

	SELECT COUNT(*) INTO related FROM employee WHERE person_id = person_fk;

	IF related.count > 0 THEN
		RAISE EXCEPTION 'Person employed by at least one employer';
	END IF;

	DELETE FROM person WHERE person = person_id;
  	DELETE FROM customer WHERE subject_fk = person_id AND subject_type_fk = 1;
  	DELETE FROM contact WHERE subject_fk = person_id AND subject_type_fk = 1;
  	DELETE FROM address WHERE subject_fk = person_id AND subject_type_fk = 1;
  	DELETE FROM subject_attribute WHERE subject_fk = person_id AND subject_type_fk = 1;
END;
$$ LANGUAGE plpgsql;